insert overwrite table jms_dm.dm_tab_in_center_package_site_summary_dt partition (dt)
select date_time                                                          as date_time               --日期
     , scan_site_code                                                     as scan_site_code          --扫描网点编号
     , input_site                                                         as input_site              --扫描网点名称
     , site_agent_code                                                    as site_agent_code         --所属代理区code
     , site_agent_name                                                    as site_agent_name         --所属代理区名称
     , network_type                                                       as network_type            -- 扫描网点类型2.转运中心,3.集散点
     , sum(way_net_falg)                                                  as way_net_total           --进港总发件量
     , sum(need_way_flag)                                                 as need_way_num            --应建包票数
     , sum(if(need_way_flag = 1 and nopack_flag = 0 and pack06_flag = 0 and pack_scan_site_code = scan_site_code, 1,
              0))                                                         as in_pack_way_num         --进港建包票数
     , sum(if(need_way_flag = 1 and nopack_flag = 1, 1, 0))               as need_no_way_num         --应建未建票数
     , count(distinct
             if(need_way_flag = 1 and nopack_flag = 0 and pack06_flag = 0 and pack_scan_site_code = scan_site_code,
                package_code, null))                                      as pack_num                --进港建包数
     , sum(if(nopack_flag = 0 and pack06_flag = 1, 1, 0))                 as first_pack_way_num      --一段码包票数
     , count(distinct
             if(nopack_flag = 0 and pack06_flag = 1, package_code, null)) as first_pack_num          --一段码包数
     , sum(if(need_way_flag = 1 and nopack_flag = 0 and pack06_flag = 0 and pack_scan_site_code = scan_site_code and
              flight_flag = 1, 1,
              0))                                                         as loop_in_pack_way_num    --循环袋包内件票数
     , count(distinct
             if(need_way_flag = 1 and nopack_flag = 0 and pack06_flag = 0 and pack_scan_site_code = scan_site_code and
                flight_flag = 1, package_code, null))                     as loop_pack_num           --循环袋包数
     , sum(if(error_pack_way_flag = 0, 1, 0))                             as error_pack_way_num      --错建包票数
     , sum(if(pack_good_flag = 1, 1, 0))                                  as need_pack_direc_way_num --文件类应建包票数
     , sum(if(pack_good_flag = 1 and nopack_flag = 0, 1, 0))              as have_pack_direc_way_num --文件类已建包票数
     , sum(if(nopack_flag = 0, 1, 0))                                     as hav_pack_way_num        --总建包票数
     , date_time                                                          as dt
from jms_dm.dm_tab_in_center_package_waybill_base_detail_dt
where dt between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
group by date_time       --日期
       , scan_site_code  --扫描网点编号
       , input_site      --扫描网点名称
       , site_agent_code --所属代理区code
       , site_agent_name
       , network_type
 distribute by dt;
